package cn.e2win.biz.base;

import java.sql.SQLException;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import cn.e2win.base.entity.SysLabelSet;
import org.springframework.stereotype.Repository;

import cn.e2win.base.entity.BaseEntity;
import cn.e2win.base.entity.SysAttach;
import cn.e2win.base.entity.SysLabel;
import cn.e2win.base.json.vo.Pager;
import cn.e2win.biz.util.StringUtils;


@Repository
public class CommonDaoImpl extends BaseDAOHibernateImpl implements CommonDao {
	
	@Override
	public void pagerApproveLog(Pager pager) {
		String sql = "SELECT * FROM sys_approve_log WHERE 1=1 ORDER BY id DESC";
		
		try {
			pager.processSql(sql);
			processPager(pager);
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}	 
	
	

	@Override
	public void pagerLabel(Pager pager) {
		String sql = 
				  " SELECT * FROM ("
				
				+ "     SELECT t1.*, IFNULL(t2.adjustCnt,0)adjustCnt, (t1.usedCnt + IFNULL(t2.adjustCnt,0)) totalCnt FROM("
				+ "         SELECT labelType, label, entityType, COUNT(1) AS usedCnt "
				+ "         FROM sys_label "
				+ "         WHERE 1=1 "
				+ "         GROUP BY labelType, label, entityType) t1 "
				+ "     LEFT JOIN sys_label_set t2 ON t1.labelType=t2.labelType AND t1.label=t2.label AND t1.entityType=t2.entityType "
				
				+ "  UNION "

				+ "     SELECT t2.labelType, t2.label, t2.entityType,IFNULL(t1.usedCnt,0)usedCnt, IFNULL(t2.adjustCnt,0)adjustCnt, (IFNULL(t1.usedCnt,0) + IFNULL(t2.adjustCnt,0)) totalCnt FROM("
				+ "         SELECT labelType, label, entityType, COUNT(1) AS usedCnt "
				+ "         FROM sys_label "
				+ "         WHERE 1=1 "
				+ "         GROUP BY labelType, label, entityType) t1 "
				+ "     RIGHT JOIN sys_label_set t2 ON t1.labelType=t2.labelType AND t1.label=t2.label AND t1.entityType=t2.entityType "
				
				
				+ ") t0"
				+ " WHERE 1=1 ";
		
		try {
			pager.processSql(sql + Pager.DEF_ORDER_BY);
			processPager(pager);
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
	
	
	@Override
	public List getSysLabelList(String className, String labelType){
		Map<String, Object> paramMap = new HashMap<String, Object>();
		paramMap.put("type", className);
		paramMap.put("labelType",labelType);

		String sql = 
				  " SELECT * FROM ("
				
				+ "     SELECT t1.*, IFNULL(t2.adjustCnt,0)adjustCnt, (t1.usedCnt + IFNULL(t2.adjustCnt,0)) totalCnt FROM("
				+ "         SELECT labelType, label, entityType, COUNT(1) AS usedCnt "
				+ "         FROM sys_label "
				+ "         WHERE 1=1 "
				+ "         GROUP BY labelType, label, entityType) t1 "
				+ "     LEFT JOIN sys_label_set t2 ON t1.labelType=t2.labelType AND t1.label=t2.label AND t1.entityType=t2.entityType "
				
				+ "  UNION "

				+ "     SELECT t2.labelType, t2.label, t2.entityType,IFNULL(t1.usedCnt,0)usedCnt, IFNULL(t2.adjustCnt,0)adjustCnt, (IFNULL(t1.usedCnt,0) + IFNULL(t2.adjustCnt,0)) totalCnt FROM("
				+ "         SELECT labelType, label, entityType, COUNT(1) AS usedCnt "
				+ "         FROM sys_label "
				+ "         WHERE 1=1 "
				+ "         GROUP BY labelType, label, entityType) t1 "
				+ "     RIGHT JOIN sys_label_set t2 ON t1.labelType=t2.labelType AND t1.label=t2.label AND t1.entityType=t2.entityType "
				
				+ " ) t0"
				+ " WHERE 1=1 AND entityType=:type and labelType=:labelType "
				+ " order by totalCnt desc";
		return getListBySql(sql,paramMap);
//		String sql = "FROM SysLabel WHERE 1=1 " ;
//				if(!StringUtils.isEmpty(className)) {
//					sql += " and entityType='" + className+"'";
//				}
//				if(!StringUtils.isEmpty(labelType)) {
//					sql += " and labelType='" + labelType+"'";
//				}
//				sql += " group by label";
//		return getObjectListByHql(sql);
	}

	@Override
	public int updateLabels(SysLabel label,String newLabel){
		
		Map<String, Object> map = new HashMap<String, Object>();
		map.put("entityType", label.getEntityType());
		map.put("labelType", label.getLabelType());
		map.put("label", label.getLabel());
		map.put("newLabel", newLabel);
		return super.updateBySql(" UPDATE sys_label SET "
				+ " label = :newLabel "
				+ " WHERE entityType=:entityType "
				+ " AND labelType=:labelType"
				+ " AND label=:label", map);
		
	}

	@Override
	public int delLabels(SysLabel label) {
		Map<String, Object> map = new HashMap<String, Object>();
		map.put("entityType", label.getEntityType());
		map.put("labelType", label.getLabelType());
		map.put("label", label.getLabel());
		return super.updateBySql(" DELETE FROM sys_label "
				+ " WHERE entityType=:entityType "
				+ " AND labelType=:labelType"
				+ " AND label=:label", map);
	}



	@Override
	public int delLabelSets(SysLabelSet sysLabelSet) {
		Map<String, Object> map = new HashMap<String, Object>();
		map.put("entityType", sysLabelSet.getEntityType());
		map.put("labelType", sysLabelSet.getLabelType());
		map.put("label", sysLabelSet.getLabel());
		return super.updateBySql(" DELETE FROM sys_label_set "
				+ " WHERE entityType=:entityType "
				+ " AND labelType=:labelType"
				+ " AND label=:label", map);
	}
	

	@Override
	public List getSysLabelList(BaseEntity entity, String labelType){
		String sql = "FROM SysLabel WHERE 1=1 "
				+ " AND entityType ='"+entity.getClass().getSimpleName()+"' "
				+ " AND entityId=" + entity.getId()
				+ " AND labelType='"+labelType+"'";
		sql += " group by label";
		return getObjectListByHql(sql);
	}


	@Override
	public SysLabelSet getSysLabelSet(SysLabel sysLabel, String label){
		String sql = "FROM SysLabelSet WHERE 1=1 "
				+ " AND entityType ='"+sysLabel.getEntityType()+"' "
				+ " AND labelType='" + sysLabel.getLabelType()+"' "
				+ " AND label='"+label+"'";
		List<SysLabelSet> sysLabelSets = getObjectListByHql(sql);
		if(sysLabelSets!=null && sysLabelSets.size()!=0 && sysLabelSets.size()<2){
			return sysLabelSets.get(0);
		}else{
			return null;
		}
//		Map<String, Object> paramMap = new HashMap<String, Object>();
//		paramMap.put("type", entity.getClass().getSimpleName());
//		paramMap.put("id", entity.getId());
//		paramMap.put("labelType",labelType);
//		String sql = "select id,label,count(1) as num from sys_label where entityType=:type and entityId=:id and labelType=:labelType group by label order by num desc";
//		return getListBySql(sql,paramMap);
	}


	@Override
	public List<SysAttach> getSysAttachList(BaseEntity entity, String attachType){
		String sql = "FROM SysAttach WHERE 1=1 "
				+ " AND entityType ='"+entity.getClass().getSimpleName()+"' "
				+ " AND entityId=" + entity.getId()
				+ " AND attachType='"+attachType+"'";
		return getObjectListByHql(sql);
	}
	


	//======================================================================================================================================================================
	//与项目相关的方法
	//======================================================================================================================================================================
	

	String labSql = "SELECT entityId,entityType, labelType, GROUP_CONCAT(label SEPARATOR '＊') AS label FROM sys_label GROUP BY entityId,entityType, labelType";
	
	
	
	@Override
	public void pagerDsRelation(BaseEntity relatedObj, Pager pager) {
		String sql = " SELECT * FROM( "
				+ " SELECT t0.dsEmpId, t0.entityType, t0.entityId, t1.* "
				+ " FROM ds_relation t0 LEFT JOIN "
				
				+ "("

				+ " SELECT t0.*"
				+ " , CONCAT('|', t1.label, '|') AS labelFocusOn "
				+ " , CONCAT('|', t2.label, '|') AS labelHelpFor "
				+ " , CONCAT('|', t3.label, '|') AS labelIndustry "
				+ " FROM ds_emp t0 "
				+ " LEFT JOIN ("+CommonStatic.labSql+") AS t1 ON t0.id = t1.entityId AND t1.entityType='DsEmp' AND t1.labelType='labelFocusOn'"
				+ " LEFT JOIN ("+CommonStatic.labSql+") AS t2 ON t0.id = t2.entityId AND t2.entityType='DsEmp' AND t2.labelType='labelHelpFor'"
				+ " LEFT JOIN ("+CommonStatic.labSql+") AS t3 ON t0.id = t3.entityId AND t3.entityType='DsEmp' AND t3.labelType='labelIndustry' "
				
				+ " ) AS t1 ON t0.dsEmpId = t1.id"
				
				+ " WHERE 2=2 ";

		String entityType = pager.getScMap().get("entityType_I");
		String entityId = pager.getScMap().get("entityId_I");
		if(relatedObj!=null && relatedObj.getId()!=null){
			sql = sql + " AND t0.entityType='" + relatedObj.getClass().getSimpleName() + "' "
			+ " AND t0.entityId =  " + relatedObj.getId();
		}else if(!StringUtils.isEmpty(entityType) && !StringUtils.isEmpty(entityId)){
			sql = sql + " AND t0.entityType='" + entityType + "' "
			+ " AND t0.entityId =  " + entityId;
		}else{
			sql = sql + " AND 1=2";
		}
		sql = sql +") t WHERE 1=1";
		try {
			pager.processSql(sql);
			processPager(pager);
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
	@Override
	public int delDsRelation(BaseEntity relatedObj){
		return super.updateBySql("DELETE FROM ds_relation where entityType = '"+relatedObj.getClass().getSimpleName()+"' AND entityId=" + relatedObj.getId(), null);
	}
	

	@Override
	public void pagerGuestRelation(BaseEntity relatedObj, Pager pager) {
		String sql = " SELECT * FROM( "
				+ " SELECT * "
				+ " FROM guest_relation t0 "
				
				+ " WHERE 2=2 ";

		String entityType = pager.getScMap().get("entityType_I");
		String entityId = pager.getScMap().get("entityId_I");
		if(relatedObj!=null && relatedObj.getId()!=null){
			sql = sql + " AND t0.entityType='" + relatedObj.getClass().getSimpleName() + "' "
			+ " AND t0.entityId =  " + relatedObj.getId();
		}else if(!StringUtils.isEmpty(entityType) && !StringUtils.isEmpty(entityId)){
			sql = sql + " AND t0.entityType='" + entityType + "' "
			+ " AND t0.entityId =  " + entityId;
		}else{
			sql = sql + " AND 1=2";
		}
		sql = sql +") t WHERE 1=1";
		try {
			pager.processSql(sql);
			processPager(pager);
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
	@Override
	public int delGuestRelation(BaseEntity relatedObj){
		return super.updateBySql("DELETE FROM guest_relation where entityType = '"+relatedObj.getClass().getSimpleName()+"' AND entityId=" + relatedObj.getId(), null);
	}



	
	
	
}
